﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using MyHotel_Common;
using MyHotel_DTO;
using DTO;

namespace MyHotel_DAO
{
    public class QuanLyNhanVienDAO : DAO
    {
        protected override object createObjectFromDatarow(DataRow row)
        {
            NhanVien ttnv = new NhanVien();
            ttnv.Ten = row["Ten"].ToString();
            ttnv.ChucVu = row["TenChucVu"].ToString();
            ttnv.SoDienThoai = row["SoDienThoai"].ToString();
            ttnv.DiaChi = row["DiaChi"].ToString();
            ttnv.NgaySinh = DateTime.Parse(row["NgaySinh"].ToString());
            ttnv.TenDangNhap = row["TenDangNhap"].ToString();
            return ttnv;
        }
        
        //kiem tra nhan vien nay co ton tai trong csdl hay khong
        public bool kiemtraMaNhanVien(int maNV)
        {
            string name_proc = "SP_GETNHANVIEN_BY_MANV";
            SqlParameter para = new SqlParameter("@MANV", maNV);
            DataTable dt = DataProvider.executeStoreProcedureQuery(name_proc, para);
            if (dt.Rows.Count != 0)
            {
                return true;
            }
            return false;
        }
        
        //ok
        public List<NhanVien> layThongTinNhanVienbyMaChucVu(int maChucVu)
        {
            string name_proc = "USP_THONGTINNHANVIEN_BY_MACHUCVU";
            SqlParameter para = new SqlParameter("@MACHUCVU", maChucVu);
            DataTable dt = DataProvider.executeStoreProcedureQuery(name_proc, para);
            return getListFromDataTable<NhanVien>(dt);
        }
        
        //ok
        public List<NhanVien> layThongTinNhanVien()
        {
            string name_proc = "USP_THONGTINNHANVIEN_BY_ALL";
            DataTable dt = DataProvider.executeStoreProcedureQuery(name_proc, null);
            return getListFromDataTable<NhanVien>(dt);
        }

        public string createTenChucVuFromTable(DataRow row)
        {
            return row["TenChucVu"].ToString();
        }
       
        //ok
        public string layTenChucVuByMa(int maChucVu)
        {
            string tenChucVu = "";
            string name_proc = "USP_lAYTENCHUCVU_BY_MACHUCVU";
            SqlParameter para = new SqlParameter("@MACHUCVU", maChucVu);
            DataTable dt = DataProvider.executeStoreProcedureQuery(name_proc, para);
            foreach (DataRow row in dt.Rows)
            {
                tenChucVu = createTenChucVuFromTable(row);
            }
            return tenChucVu;
        }
        
        //ok
        public bool themNhanVien(NhanVien ttnv)
        {
            int maTaiKhoan;
            string ngaySinh = string.Format("{0:ddMMyy}", ttnv.NgaySinh);
            int maChucVu = layMaChucVuByTenChucVu(ttnv.ChucVu);
            
            TaiKhoan taiKhoan = new TaiKhoan();
            taiKhoan.TenDangNhap = ttnv.TenDangNhap;
            taiKhoan.MatKhau = ngaySinh;
            taiKhoan.MaChucVu = maChucVu;
            bool result = new TaiKhoanDAO().themTaiKhoan(taiKhoan);

            if (result == false)
                return false;

            maTaiKhoan = layMaTaiKhoanByTenTaiKhoan(ttnv.TenDangNhap);

            string nameProc = "SP_THEMNHANVIEN";
            SqlParameter[] para = new SqlParameter[5];
            para[0] = new SqlParameter("@TEN", ttnv.Ten);
            para[1] = new SqlParameter("@NGAYSINH", ttnv.NgaySinh);
            para[2] = new SqlParameter("@DIACHI", ttnv.DiaChi);
            para[3] = new SqlParameter("@SODIENTHOAI", ttnv.SoDienThoai);
            para[4] = new SqlParameter("@MATAIKHOAN", maTaiKhoan);
                        
            return DataProvider.executeStoreProcedureNonQuery(nameProc, para) == 1;
        }
        
        public int layMaNVbySoDienThoai(string soDienThoai)
        {
            string name_proc = "USP_MANV_BY_SODIENTHOAI";
            SqlParameter para = new SqlParameter("@SODIENTHOAI", soDienThoai);
            DataTable dt = DataProvider.executeStoreProcedureQuery(name_proc, para);
            return int.Parse(dt.Rows[0]["MaNV"].ToString());
        }
       
        public int layMaTaiKhoanbyTenDangNhap(string tenDangNhap)
        {
            string name_proc = "USP_MATAIKHOAN_TENDANGNHAP";
            SqlParameter para = new SqlParameter("@TENDANGNHAP", tenDangNhap);
            DataTable dt = DataProvider.executeStoreProcedureQuery(name_proc, para);
            return int.Parse(dt.Rows[0]["MaTaiKhoan"].ToString());
        }
   
        public bool suaNhanVien(NhanVien ttnv)
        {
            int maTaiKhoan = layMaTaiKhoanByTenTaiKhoan(ttnv.TenDangNhap);
            int maChucVu = layMaChucVuByTenChucVu(ttnv.ChucVu);

            string nameProc = "SP_UPDATE_TAIKHOAN_CHANGE_MACHUCVU";
            SqlParameter[] para = new SqlParameter[2];
            para[0] = new SqlParameter("@MACHUCVU", maChucVu);
            para[1] = new SqlParameter("@MATK", maTaiKhoan);
                        
            return DataProvider.executeStoreProcedureNonQuery(nameProc, para) == 1;
        }
 
        public bool xoaNhanVien(NhanVien ttnv)
        {
            int maNhanVien = layMaNVbySoDienThoai(ttnv.SoDienThoai);
            int maTaiKhoan = layMaTaiKhoanByTenTaiKhoan(ttnv.TenDangNhap);

            string nameProc = "SP_DELETE_NHANVIEN_BY_MANV";
            SqlParameter para = new SqlParameter("@MANV", maNhanVien);
            int result = DataProvider.executeStoreProcedureNonQuery(nameProc, para);
            if (result == 0)
                return false;

            nameProc = "SP_DELETE_TAIKHOAN_BY_MATK";
            para = new SqlParameter("@MATK", maTaiKhoan);
            result = DataProvider.executeStoreProcedureNonQuery(nameProc, para);
            if (result == 0)
                return false;

            return true;
        }
   
        public bool resetMatKhau(NhanVien ttnv)
        {
            int maTaiKhoan = layMaTaiKhoanByTenTaiKhoan(ttnv.TenDangNhap);
            string nameProc = "SP_UPDATE_TAIKHOAN_CHANGE_MATKHAU";
            SqlParameter[] para = new SqlParameter[2];
            para[0] = new SqlParameter("@MATK", string.Format("{0:ddMMyy}", ttnv.NgaySinh));
            para[1] = new SqlParameter("@MATKHAU", maTaiKhoan);
            
            
            return DataProvider.executeStoreProcedureNonQuery(nameProc, para) == 1;
        }
 
        public bool kiemtraNhanVien_TonTai_DienThoai(string soDT)
        {
            string name_proc = "USP_KIEMTRANHANVIEN_TONTAI_DIENTHOAI";
            SqlParameter para = new SqlParameter("@DIENTHOAI", soDT);
            DataTable dt = DataProvider.executeStoreProcedureQuery(name_proc, para);
            if (dt.Rows.Count == 1)
            {
                return true;
            }
            return false;
        }
 
        public bool kiemtraNhanVien_TonTai_TenDangNhap(string tenDangNhap)
        {
            string name_proc = "USP_KIEMTRANHANVIEN_TONTAI_TENDANGNHAP";
            SqlParameter para = new SqlParameter("@TENDANGNHAP", tenDangNhap);
            DataTable dt = DataProvider.executeStoreProcedureQuery(name_proc, para);
            if (dt.Rows.Count == 1)
            {
                return true;
            }
            return false;
        }
 
        public int layMaChucVuByTenChucVu(string tenChucVu)
        {
            string name_proc = "USP_LAYMACHUCVU_BY_TENCHUCVU";
            SqlParameter para = new SqlParameter("@TENCHUCVU", tenChucVu);
            DataTable dt = DataProvider.executeStoreProcedureQuery(name_proc, para);
            return int.Parse(dt.Rows[0]["MaChucVu"].ToString());
        }
 
        public int layMaTaiKhoanByTenTaiKhoan(string tenDangNhap)
        {
            string name_proc = "USP_LAYMATAIKHOAN_BY_TENDANGNHAP";
            SqlParameter para = new SqlParameter("@TENDANGNHAP", tenDangNhap);
            DataTable dt = DataProvider.executeStoreProcedureQuery(name_proc, para);
            return int.Parse(dt.Rows[0]["MaTaiKhoan"].ToString());
        }
    }
}
